package net.huashitong.oversee.dao;

import com.sirdc.modules.core.dao.GenericQuery;
import com.sirdc.modules.core.dao.StringDao;
import com.sirdc.modules.utils.ObjectUtils;
import com.sirdc.modules.utils.StringUtils;
import jodd.util.StringUtil;
import net.huashitong.oversee.entity.SupervisionTask;
import net.huashitong.oversee.filter.SupervisionTaskFilter;
import net.huashitong.user.entity.BnpUser;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.orm.hibernate4.SessionFactoryUtils;
import org.springframework.stereotype.Repository;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.List;

@Repository
public class SupervisionTaskDao extends StringDao<SupervisionTask> {


    /**
     * 任务清单页面查询主入口
     *
     * @param filter
     * @return
     */
    public List<SupervisionTask> getMissinTask(SupervisionTaskFilter filter) {
        GenericQuery query = createQuery("obj");
        if (StringUtils.isNotBlank(filter.getSysId())) {
            query.append(" and sysId =:sysId").setParam("sysId",
                    filter.getSysId());
        }
      /*  if (filter.getTasktimes()!=null&&!"".equals(filter.getTasktimes()))
            query.append(" and taskTimes =:taskTimes").setParam("taskTimes",filter.getTasktimes());*/
        if (filter.getCreateDate() != null && !"".equals(filter.getCreateDate()))
            query.append(" and createDate like:createDate").setParam("createDate",
                    StringUtils.getLikewords(filter.getCreateDate()));
        if (filter.getTaskName() != null && !"".equals(filter.getTaskName()))
            query.append(" and taskName like:taskName").setParam("taskName",
                    StringUtils.getLikewords(filter.getTaskName()));
        if (filter.getProjectName() != null && !"".equals(filter.getProjectName()))
            query.append(" and projectName like:projectName").setParam("projectName",
                    StringUtils.getLikewords(filter.getProjectName()));
        if (filter.getTaskContent() != null && !"".equals(filter.getTaskContent()))
            query.append(" and taskContent =:taskContent").setParam("taskContent", filter.getTaskContent());
        if (StringUtils.isNotBlank(filter.getValid())) { //有效任务
            query.append(" and aaa001 =:aaa001").setParam("aaa001", filter.getValid());
        } else {
            query.append(" and aaa001 =:aaa001").setParam("aaa001", "1");
        }
        if (StringUtils.isNotBlank(filter.getTaskResult())) {
            query.append(" and taskResult=:TaskResult").setParam("TaskResult", filter.getTaskResult());
        }
        if (filter.getStartDate() != null && !"".equals(filter.getStartDate()))
            query.append(" and startDate =:startDate").setParam("startDate", filter.getStartDate());
        if (filter.getEndDate() != null && !"".equals(filter.getEndDate()))
            query.append(" and endDate =:endDate").setParam("endDate", filter.getEndDate());
        if (filter.getRespResult() != null && !"".equals(filter.getRespResult()))
            query.append(" and respResult =:respResult").setParam("respResult", filter.getRespResult());
        if (filter.getSupervisionResult() != null && !"".equals(filter.getSupervisionResult()))
            query.append(" and supervisionResult =:supervisionResult").setParam("supervisionResult", filter.getSupervisionResult());
        if (filter.getNodeId() != null && !"".equals(filter.getNodeId()))
            query.append(" and nodeId =:nodeId").setParam("nodeId", filter.getNodeId());
        if (filter.getResDept() != null && !"".equals(filter.getResDept()))
            query.append(" and resDept like:resDept").setParam("resDept",
                    StringUtils.getLikewords(filter.getResDept()));

        if (filter.getResUser() != null && !"".equals(filter.getResUser()))
            query.append(" and resUser like:resUser").setParam("resUser",
                    StringUtils.getLikewords(filter.getResUser()));
        if (filter.getOperatorUser() != null && !"".equals(filter.getOperatorUser()))
            query.append(" and operatorUser like:operatorUser").setParam("operatorUser",
                    StringUtils.getLikewords(filter.getOperatorUser()));
        if (filter.getInvolveUnit() != null && !"".equals(filter.getInvolveUnit()))
            query.append(" and involveUnit =:involveUnit").setParam("involveUnit", filter.getInvolveUnit());
        if (filter.getFeedback() != null && !"".equals(filter.getFeedback()))
            query.append(" and feedback like:feedback").setParam("feedback",
                    StringUtils.getLikewords(filter.getFeedback()));
        if (filter.getTasktimes() != null && !"".equals(filter.getTasktimes()))
            query.append(" and tasktimes =:tasktimes").setParam("tasktimes", filter.getTasktimes());
        if ("4".equals(filter.getBranchType())) {
            query.append(" and obj.phones like:phones ").setParam("phones",
                    StringUtils.getLikewords(filter.getUserinfo()));
        }
        if (!"2".equals(filter.getBranchType()) && !"4".equals(filter.getBranchType()) && "经办人".equals(filter.getRole())) {
            query.append(" and obj.operatorPhone =:operatorPhone ").setParam("operatorPhone",
                    filter.getUserinfo());
        }
        if (!"2".equals(filter.getBranchType()) && !"4".equals(filter.getBranchType()) && "责任人".equals(filter.getRole())) {
            query.append(" and obj.resPhone =:resPhone ").setParam("resPhone",
                    filter.getUserinfo());
        }
        //模糊搜索
        if (StringUtils.isNotBlank(filter.getSearchWord())) {
            query.append(" and ( obj.taskName like:taskName or " +
                    "obj.taskContent like:taskContent or " +
                    "obj.sysId like:sysId or " +
                    "obj.resUser like:resUser or " +
                    "obj.inspectors like:inspectors or " +
                    "obj.projectName like:projectName or " +
                    "obj.nodeName like:nodeName or " +
                    "obj.resDept like:resDept or " +
                    "obj.operatorUser like:operatorUser )").setParam("taskName",
                    StringUtils.getLikewords(filter.getSearchWord())).setParam("taskContent",
                    StringUtils.getLikewords(filter.getSearchWord())).setParam("sysId",
                    StringUtils.getLikewords(filter.getSearchWord())).setParam("resUser",
                    StringUtils.getLikewords(filter.getSearchWord())).setParam("inspectors",
                    StringUtils.getLikewords(filter.getSearchWord())).setParam("projectName",
                    StringUtils.getLikewords(filter.getSearchWord())).setParam("nodeName",
                    StringUtils.getLikewords(filter.getSearchWord())).setParam("resDept",
                    StringUtils.getLikewords(filter.getSearchWord())).setParam("operatorUser",
                    StringUtils.getLikewords(filter.getSearchWord()));
        }
        if (StringUtils.isNotBlank(filter.getTackFlag())) {
            query.append(" and tackFlag =:tackFlag").setParam("tackFlag", filter.getTackFlag());
        }

//        if (StringUtils.isNotBlank(filter.getAiSort()) && "1".equals(filter.getAiSort())) {
//            if (filter.getPaging().getSortCol() == null || "".equals(filter.getPaging().getSortCol())) {
//                query.append(" and taskResult=:TaskResult").setParam("TaskResult", "0");
//                query.append(" and aaa001=:aaa001").setParam("aaa001", "1");
//                query.append(" ORDER BY  obj.planEndDate asc  ");
//            }
//        } else {
//            if (filter.getPaging() != null) {
//                if (filter.getPaging().getSortCol() == null || "".equals(filter.getPaging().getSortCol())) {
//                    query.append(" ORDER BY  str_to_date( CONCAT(obj.updDate,' ',obj.updTime),'%Y-%m-%d %H:%i:%s') desc  ");
//                }
//            }
//        }
        if (filter.getPaging() != null) {
            if (filter.getPaging().getSortCol() == null || "".equals(filter.getPaging().getSortCol())) {
                query.append(" ORDER BY obj.taskResult asc, obj.aaa001 desc ,obj.planEndDate asc ");
            }
        }

        return query.listResult(filter);
    }

    //************************************分管领导查看督查任务***************************************

    public List<SupervisionTask> queryByFilterInLeader(SupervisionTaskFilter filter) {
        StringBuffer sql = new StringBuffer();
        sql.append("" +
                "select \n" +
                "t.sys_id as sysId,\n" +
                "t.create_date as createDate,\n" +
                "t.create_time as createTime,\n" +
                "t.create_user as createUser,\n" +
                "t.upd_date as updDate,\n" +
                "t.upd_time as updTime,\n" +
                "t.upd_user as updUser,\n" +
                "t.group_type as groupType,\n" +
                "t.node_detail as nodeDetail,\n" +
                "t.node_id as nodeId,\n" +
                "t.node_name as nodeName,\n" +
                "t.operator_phone as operatorPhone,\n" +
                "t.operator_user as operatorUser,\n" +
                "t.project_id as projectId,\n" +
                "t.project_name as projectName,\n" +
                "t.res_dept as resDept,\n" +
                "t.res_phone as resPhone,\n" +
                "t.res_user as resUser,\n" +
                "t.task_result as taskResult,\n" +
                "t.end_date as endDate,\n" +
                "t.inspectors as inspectors,\n" +
                "t.phones as phones,\n" +
                "t.resp_result as respResult,\n" +
                "t.response_time as responseTime,\n" +
                "t.start_date as startDate,\n" +
                "t.supervision_result as supervisionResult,\n" +
                "t.task_content as taskContent,\n" +
                "t.task_date as taskDate,\n" +
                "t.task_name as taskName,\n" +
                "t.expiration_date as expirationDate,\n" +
                "t.aaa001 as aaa001,\n" +
                "t.group_id as groupId,\n" +
                "t.isfeedback as isfeedback,\n" +
                "t.plan_end_date as planEndDate,\n" +
                "t.plan_start_date as planStartDate,\n" +
                "t.resp_score as respScore,\n" +
                "t.supervision_score as supervisionScore,\n" +
                "t.task_stage as taskStage,\n" +
                "t.isrebuild as isrebuild,\n" +
                "t.tasktimes as tasktimes,\n" +
                "t.involve_unit as involveUnit,\n" +
                "t.aaa002 as aaa002,\n" +
                "t.taskjob_type as taskjobType,\n" +
                "t.userid as userid,\n" +
                "t.remarks as remarks,\n" +
                "t.feedback as feedback,\n" +
                "t.organizer as organizer,\n" +
                "t.deadline as deadline,\n" +
                "t.supervision_ask as supervisionAsk,\n" +
                "t.isfeedback_to_tack as isfeedbackToTack,\n" +
                "t.node_sys_id as nodeSysId,\n" +
                "t.overdue_days as overdueDays,\n" +
                "t.tack_flag as tackFlag,\n" +
                "t.taxproof as taxproof,\n" +
                "t.report_user as reportUser,\n" +
                "t.report_user_id as reportUserId,\n" +
                "t.report_user_phone as reportUserPhone,\n" +
                "t.approval_tack_date as approvalTackDate,\n" +
                "t.approval_tack_text as approvalTackText,\n" +
                "t.finish_tack_date as finishTackDate,\n" +
                "t.finish_tack_text as finishTackText,\n" +
                "t.tack_percent as tackPercent \n" +
                "from t_bnp_new_project p, t_bnp_supervision_task t, t_bnp_user u \n" +
                "where p.sys_id = t.project_id and p.res_user = u.name ");

        if (StringUtils.isNotBlank(filter.getUserinfo())) {
            filter.setUserPhone(filter.getUserinfo());
        }
        if (StringUtils.isNotBlank(filter.getUserPhone())) {
            sql.append(" and u.phone = '" + filter.getUserPhone() + "'");
        }
        if (filter.getTaskName() != null && !"".equals(filter.getTaskName()))
            sql.append(" and t.task_name like '" + StringUtils.getLikewords(filter.getTaskName()) + "'");
        if (filter.getProjectName() != null && !"".equals(filter.getProjectName()))
            sql.append(" and t.project_name like '" + StringUtils.getLikewords(filter.getProjectName()) + "'");
        if (filter.getTaskContent() != null && !"".equals(filter.getTaskContent()))
            sql.append(" and t.task_content like '" + StringUtils.getLikewords(filter.getTaskContent()) + "'");
        if (StringUtils.isNotBlank(filter.getValid())) { //有效任务
            sql.append(" and t.aaa001 = '" + filter.getValid() + "'");
        } else {
            sql.append(" and t.aaa001 = '1' ");
        }
        if (StringUtils.isNotBlank(filter.getTaskResult())) {
            sql.append(" and t.task_result = '" + filter.getTaskResult() + "'");
        }
        if (filter.getCreateDate() != null && !"".equals(filter.getCreateDate()))
            sql.append(" and t.create_date like '" + StringUtils.getLikewords(filter.getCreateDate()) + "'");
        if (filter.getStartDate() != null && !"".equals(filter.getStartDate()))
            sql.append(" and t.start_date = '" + filter.getStartDate() + "'");
        if (filter.getEndDate() != null && !"".equals(filter.getEndDate()))
            sql.append(" and t.end_date = '" + filter.getEndDate() + "'");
        if (filter.getRespResult() != null && !"".equals(filter.getRespResult()))
            sql.append(" and t.resp_result = '" + filter.getRespResult() + "'");
        if (filter.getSupervisionResult() != null && !"".equals(filter.getSupervisionResult()))
            sql.append(" and t.supervision_result = '" + filter.getSupervisionResult() + "'");
        if (filter.getNodeId() != null && !"".equals(filter.getNodeId()))
            sql.append(" and t.node_id = '" + filter.getNodeId() + "'");
        if (filter.getResDept() != null && !"".equals(filter.getResDept()))
            sql.append(" and t.res_dept like '" + StringUtils.getLikewords(filter.getResDept()) + "'");
        if (filter.getResUser() != null && !"".equals(filter.getResUser()))
            sql.append(" and t.res_user like '" + StringUtils.getLikewords(filter.getResUser()) + "'");
        if (filter.getOperatorUser() != null && !"".equals(filter.getOperatorUser()))
            sql.append(" and t.operator_user like '" + StringUtils.getLikewords(filter.getOperatorUser()) + "'");
        if (filter.getInvolveUnit() != null && !"".equals(filter.getInvolveUnit()))
            sql.append(" and t.involve_unit = '" + filter.getInvolveUnit() + "'");
        if (filter.getFeedback() != null && !"".equals(filter.getFeedback()))
            sql.append(" and t.feedback like '" + StringUtils.getLikewords(filter.getFeedback()) + "'");
        if (filter.getTasktimes() != null && !"".equals(filter.getTasktimes()))
            sql.append(" and t.tasktimes = '" + filter.getTasktimes() + "'");


        //督查任务Id
        if (filter.getSysId() != null && !"".equals(filter.getSysId()))
            sql.append(" and t.sys_id = '" + filter.getSysId() + "'");

        //模糊搜索
        if (StringUtils.isNotBlank(filter.getSearchWord())) {
            sql.append(" and ( t.task_name like '" + StringUtils.getLikewords(filter.getSearchWord()) + "' or " +
                    "t.task_content like '" + StringUtils.getLikewords(filter.getSearchWord()) + "' or " +
                    "t.sys_id like '" + StringUtils.getLikewords(filter.getSearchWord()) + "' or " +
                    "t.res_user like '" + StringUtils.getLikewords(filter.getSearchWord()) + "' or " +
                    "t.inspectors like '" + StringUtils.getLikewords(filter.getSearchWord()) + "' or " +
                    "t.project_name like '" + StringUtils.getLikewords(filter.getSearchWord()) + "' or " +
                    "t.node_name like '" + StringUtils.getLikewords(filter.getSearchWord()) + "' or " +
                    "t.res_dept like '" + StringUtils.getLikewords(filter.getSearchWord()) + "' or " +
                    "t.operator_user like '" + StringUtils.getLikewords(filter.getSearchWord()) + "'  )");
        }
        if (StringUtils.isNotBlank(filter.getTackFlag())) {
            sql.append(" and t.tack_flag = '" + filter.getTackFlag() + "'");
        }

        //关联表做分页处理
        if (filter.getPaging() != null) {
            if (filter.getPaging().getSortCol() == null || "".equals(filter.getPaging().getSortCol())) {
                sql.append(" ORDER BY  str_to_date( CONCAT(t.upd_date,' ',t.upd_time),'%Y-%m-%d %H:%i:%s') desc  ");
            }
        }
        String hql = sql.toString();
        hql = new StringBuffer(" select count(1) ").append(" from ( ")
                .append(hql).append(" ) temp ").toString();
        Query getCount = getSession().createSQLQuery(hql);
        if (filter.getPaging() != null) {
            int count = ((Number) getCount.uniqueResult()).intValue();
            int resultSize = count;
            int size = filter.getPaging().getPageSize();
            int page = filter.getPaging().getCurrentPage();
            int MaxPage = resultSize / size;
            if (!(resultSize % size == 0)) {
                MaxPage = (resultSize / size) + 1;
            }
            if (page > MaxPage) {
//                filter.getPaging().setCurrentPage(MaxPage);
                return null;
            }
            filter.getPaging().setRecords(resultSize);
        }

        SQLQuery query = getSession().createSQLQuery(sql.toString());
        query.setResultTransformer(Transformers.aliasToBean(SupervisionTask.class));
        if (filter.getPaging() != null) {
            query.setFirstResult(filter.getPaging().getStartRow());
            query.setMaxResults(filter.getPaging().getPageSize());
        }
        List<SupervisionTask> result = query.list();
        return result;
    }


    /**
     * 查询督查任务（类似上面方法除了没有分页）
     *
     * @param filter
     * @return
     */
    public List<SupervisionTask> getTaskListsNoPage(SupervisionTaskFilter filter) {
        GenericQuery query = createQuery("obj");
        if (StringUtils.isNotBlank(filter.getSysId())) {
            query.append(" and sysId =:sysId").setParam("sysId",
                    filter.getSysId());
        }
        if (StringUtils.isNotBlank(filter.getNodeSysId())) {
            query.append(" and nodeSysId =:nodeSysId").setParam("nodeSysId",
                    filter.getNodeSysId());
        }
        if (filter.getProjectId() != null && !"".equals(filter.getProjectId()))
            query.append(" and projectId =:projectId").setParam("projectId", filter.getProjectId());
        if (filter.getTaskName() != null && !"".equals(filter.getTaskName()))
            query.append(" and taskName like:taskName").setParam("taskName",
                    StringUtils.getLikewords(filter.getTaskName()));
        if (filter.getProjectName() != null && !"".equals(filter.getProjectName()))
            query.append(" and projectName like:projectName").setParam("projectName",
                    StringUtils.getLikewords(filter.getProjectName()));
        if (filter.getTaskContent() != null && !"".equals(filter.getTaskContent()))
            query.append(" and taskContent =:taskContent").setParam("taskContent", filter.getTaskContent());
        //有效任务
        if (StringUtils.isNotBlank(filter.getValid())) {
            query.append(" and aaa001 =:aaa001").setParam("aaa001", filter.getValid());
        }
        if (StringUtils.isNotBlank(filter.getTaskResult())) {
            query.append(" and taskResult=:TaskResult").setParam("TaskResult", filter.getTaskResult());
        }
        if (filter.getStartDate() != null && !"".equals(filter.getStartDate()))
            query.append(" and startDate =:startDate").setParam("startDate", filter.getStartDate());
        if (filter.getEndDate() != null && !"".equals(filter.getEndDate()))
            query.append(" and endDate =:endDate").setParam("endDate", filter.getEndDate());
        if (filter.getRespResult() != null && !"".equals(filter.getRespResult()))
            query.append(" and respResult =:respResult").setParam("respResult", filter.getRespResult());
        if (filter.getSupervisionResult() != null && !"".equals(filter.getSupervisionResult()))
            query.append(" and supervisionResult =:supervisionResult").setParam("supervisionResult", filter.getSupervisionResult());
        if (filter.getNodeId() != null && !"".equals(filter.getNodeId()))
            query.append(" and nodeId =:nodeId").setParam("nodeId", filter.getNodeId());
        if (filter.getResDept() != null && !"".equals(filter.getResDept()))
            query.append(" and resDept =:resDept").setParam("resDept", filter.getResDept());
        if (filter.getResUser() != null && !"".equals(filter.getResUser()))
            query.append(" and resUser =:resUser").setParam("resUser", filter.getResUser());
        if (filter.getOperatorUser() != null && !"".equals(filter.getOperatorUser()))
            query.append(" and operatorUser =:operatorUser").setParam("operatorUser", filter.getOperatorUser());
        if (filter.getInvolveUnit() != null && !"".equals(filter.getInvolveUnit()))
            query.append(" and involveUnit =:involveUnit").setParam("involveUnit", filter.getInvolveUnit());
        if (filter.getFeedback() != null && !"".equals(filter.getFeedback()))
            query.append(" and feedback like:feedback").setParam("feedback",
                    StringUtils.getLikewords(filter.getFeedback()));
        if (filter.getTasktimes() != null && !"".equals(filter.getTasktimes()))
            query.append(" and tasktimes =:tasktimes").setParam("tasktimes", filter.getTasktimes());
        if ("4".equals(filter.getBranchType())) {
            query.append(" and obj.phones like:phones ").setParam("phones",
                    StringUtils.getLikewords(filter.getUserinfo()));
        }

        if (!"2".equals(filter.getBranchType()) && !"4".equals(filter.getBranchType()) && "经办人".equals(filter.getRole())) {
            query.append(" and obj.operatorPhone =:operatorPhone ").setParam("operatorPhone",
                    filter.getUserinfo());
        }
        if (!"2".equals(filter.getBranchType()) && !"4".equals(filter.getBranchType()) && "责任人".equals(filter.getRole())) {
            query.append(" and obj.resPhone =:resPhone ").setParam("resPhone",
                    filter.getUserinfo());
        }
        if (filter.getPaging() != null) {
            if (filter.getPaging().getSortCol() == null || "".equals(filter.getPaging().getSortCol())) {
                query.append(" ORDER BY obj.taskResult asc, obj.aaa001 desc ,obj.planEndDate asc ");
            }
        }

        return query.listResult();
    }


    /**
     * 根据经办人名字手机号查询督查任务
     *
     * @param phone
     * @param name
     * @return
     */
    public List<SupervisionTask> queryByOperator(String phone, String name) {
        GenericQuery query = createQuery("obj");
        query.append(" and obj.operatorUser = :operatorUser ").setParam("operatorUser", name);
        query.append(" and obj.operatorPhone = :operatorPhone order By obj.nodeId desc ").setParam("operatorPhone", phone);
        return query.listResult();
    }

    /**
     * 根据责任人名字手机号查询督查任务
     *
     * @param phone
     * @param name
     * @return
     */
    public List<SupervisionTask> queryByResUser(String phone, String name) {
        GenericQuery query = createQuery("obj");
        query.append(" and obj.resUser = :resUser ").setParam("resUser", name);
        query.append(" and obj.resPhone = :resPhone order By obj.nodeId desc ").setParam("resPhone", phone);
        return query.listResult();
    }

    public List<SupervisionTask> queryTasksByIds(List<String> ids) {
        GenericQuery query = createQuery("obj");
        query.append("and obj.sysId in(:ids)").setParamList("ids", ids);
        return query.listResult();
    }

    /**
     * 任务清单页面查询主入口
     *
     * @param filter
     * @return
     */
    public int getMissinTaskCount(SupervisionTaskFilter filter) {
        GenericQuery query = createQuery("obj");
        if (StringUtils.isNotBlank(filter.getSysId())) {
            query.append(" and sysId =:sysId").setParam("sysId",
                    filter.getSysId());
        }
      /*  if (filter.getTasktimes()!=null&&!"".equals(filter.getTasktimes()))
            query.append(" and taskTimes =:taskTimes").setParam("taskTimes",filter.getTasktimes());*/
        if (filter.getTaskName() != null && !"".equals(filter.getTaskName()))
            query.append(" and taskName like:taskName").setParam("taskName",
                    StringUtils.getLikewords(filter.getTaskName()));
        if (filter.getProjectName() != null && !"".equals(filter.getProjectName()))
            query.append(" and projectName like:projectName").setParam("projectName",
                    StringUtils.getLikewords(filter.getProjectName()));
        if (filter.getTaskContent() != null && !"".equals(filter.getTaskContent()))
            query.append(" and taskContent =:taskContent").setParam("taskContent", filter.getTaskContent());
        if (StringUtils.isNotBlank(filter.getValid())) //有效任务
        {
            query.append(" and aaa001 =:aaa001").setParam("aaa001", filter.getValid());
        }
        if (StringUtils.isNotBlank(filter.getTaskResult())) {
            query.append(" and taskResult=:TaskResult").setParam("TaskResult", filter.getTaskResult());
        }
        if (StringUtils.isNotBlank(filter.getTasktimes())) {
            query.append(" and tasktimes=:tasktimes").setParam("tasktimes", filter.getTasktimes());
        }

        if (filter.getStartDate() != null && !"".equals(filter.getStartDate()))
            query.append(" and startDate =:startDate").setParam("startDate", filter.getStartDate());
        if (filter.getEndDate() != null && !"".equals(filter.getEndDate()))
            query.append(" and endDate =:endDate").setParam("endDate", filter.getEndDate());
        if (filter.getRespResult() != null && !"".equals(filter.getRespResult()))
            query.append(" and respResult =:respResult").setParam("respResult", filter.getRespResult());
        if (filter.getSupervisionResult() != null && !"".equals(filter.getSupervisionResult()))
            query.append(" and supervisionResult =:supervisionResult").setParam("supervisionResult", filter.getSupervisionResult());
        if (filter.getNodeId() != null && !"".equals(filter.getNodeId()))
            query.append(" and nodeId =:nodeId").setParam("nodeId", filter.getNodeId());
        if (filter.getResDept() != null && !"".equals(filter.getResDept()))
            query.append(" and resDept =:resDept").setParam("resDept", filter.getResDept());
        if (filter.getResUser() != null && !"".equals(filter.getResUser()))
            query.append(" and resUser =:resUser").setParam("resUser", filter.getResUser());
        if (filter.getOperatorUser() != null && !"".equals(filter.getOperatorUser()))
            query.append(" and operatorUser =:operatorUser").setParam("operatorUser", filter.getOperatorUser());
        if (filter.getInvolveUnit() != null && !"".equals(filter.getInvolveUnit()))
            query.append(" and involveUnit =:involveUnit").setParam("involveUnit", filter.getInvolveUnit());
        if (filter.getFeedback() != null && !"".equals(filter.getFeedback()))
            query.append(" and feedback like:feedback").setParam("feedback",
                    StringUtils.getLikewords(filter.getFeedback()));
        if (filter.getTackFlag() != null && !"".equals(filter.getTackFlag()))
            query.append(" and tackFlag =:tackFlag").setParam("tackFlag", filter.getTackFlag());

        if ("4".equals(filter.getBranchType())) {
            query.append(" and obj.phones like:phones ").setParam("phones",
                    StringUtils.getLikewords(filter.getUserinfo()));
        }

        if (!"2".equals(filter.getBranchType()) && !"4".equals(filter.getBranchType()) && "经办人".equals(filter.getRole())) {
            query.append(" and obj.operatorPhone =:operatorPhone ").setParam("operatorPhone",
                    filter.getUserinfo());
        }
        if (!"2".equals(filter.getBranchType()) && !"4".equals(filter.getBranchType()) && "责任人".equals(filter.getRole())) {
            query.append(" and obj.resPhone =:resPhone ").setParam("resPhone",
                    filter.getUserinfo());
        }

        if (filter.getPaging() != null) {
            if (filter.getPaging().getSortCol() == null || "".equals(filter.getPaging().getSortCol())) {
                query.append(" ORDER BY  str_to_date( CONCAT(obj.updDate,' ',obj.updTime),'%Y-%m-%d %H:%i:%s') desc  ");
            }
        }
        List<SupervisionTask> list = query.listResult();
        if (ObjectUtils.isNotBlank(list)) {
            return list.size();
        } else {
            return 0;
        }
    }

    public int getMissinTaskCount1(SupervisionTaskFilter filter) {
        StringBuffer sql = new StringBuffer();
        sql.append("" +
                "select \n" +
                "t.sys_id as sysId,\n" +
                "t.create_date as createDate,\n" +
                "t.create_time as createTime,\n" +
                "t.create_user as createUser,\n" +
                "t.upd_date as updDate,\n" +
                "t.upd_time as updTime,\n" +
                "t.upd_user as updUser,\n" +
                "t.group_type as groupType,\n" +
                "t.node_detail as nodeDetail,\n" +
                "t.node_id as nodeId,\n" +
                "t.node_name as nodeName,\n" +
                "t.operator_phone as operatorPhone,\n" +
                "t.operator_user as operatorUser,\n" +
                "t.project_id as projectId,\n" +
                "t.project_name as projectName,\n" +
                "t.res_dept as resDept,\n" +
                "t.res_phone as resPhone,\n" +
                "t.res_user as resUser,\n" +
                "t.task_result as taskResult,\n" +
                "t.end_date as endDate,\n" +
                "t.inspectors as inspectors,\n" +
                "t.phones as phones,\n" +
                "t.resp_result as respResult,\n" +
                "t.response_time as responseTime,\n" +
                "t.start_date as startDate,\n" +
                "t.supervision_result as supervisionResult,\n" +
                "t.task_content as taskContent,\n" +
                "t.task_date as taskDate,\n" +
                "t.task_name as taskName,\n" +
                "t.expiration_date as expirationDate,\n" +
                "t.aaa001 as aaa001,\n" +
                "t.group_id as groupId,\n" +
                "t.isfeedback as isfeedback,\n" +
                "t.plan_end_date as planEndDate,\n" +
                "t.plan_start_date as planStartDate,\n" +
                "t.resp_score as respScore,\n" +
                "t.supervision_score as supervisionScore,\n" +
                "t.task_stage as taskStage,\n" +
                "t.isrebuild as isrebuild,\n" +
                "t.tasktimes as tasktimes,\n" +
                "t.involve_unit as involveUnit,\n" +
                "t.aaa002 as aaa002,\n" +
                "t.taskjob_type as taskjobType,\n" +
                "t.userid as userid,\n" +
                "t.remarks as remarks,\n" +
                "t.feedback as feedback,\n" +
                "t.organizer as organizer,\n" +
                "t.deadline as deadline,\n" +
                "t.supervision_ask as supervisionAsk,\n" +
                "t.isfeedback_to_tack as isfeedbackToTack,\n" +
                "t.node_sys_id as nodeSysId,\n" +
                "t.overdue_days as overdueDays,\n" +
                "t.tack_flag as tackFlag,\n" +
                "t.taxproof as taxproof,\n" +
                "t.report_user as reportUser,\n" +
                "t.report_user_id as reportUserId,\n" +
                "t.report_user_phone as reportUserPhone,\n" +
                "t.approval_tack_date as approvalTackDate,\n" +
                "t.approval_tack_text as approvalTackText,\n" +
                "t.finish_tack_date as finishTackDate,\n" +
                "t.finish_tack_text as finishTackText,\n" +
                "t.tack_percent as tackPercent \n" +
                "from t_bnp_new_project p, t_bnp_supervision_task t, t_bnp_user u \n" +
                "where p.sys_id = t.project_id and p.res_user = u.name and t.aaa001 = '1' ");
        if (filter.getTackFlag() != null && !"".equals(filter.getTackFlag()))
            sql.append(" and t.tack_flag = '" + filter.getTackFlag() + "'");
        if (StringUtils.isNotBlank(filter.getUserinfo())) {
            sql.append(" and u.phone = '" + filter.getUserinfo() + "'");
        }
        String hql = sql.toString();
        hql = new StringBuffer(" select count(1) ").append(" from ( ")
                .append(hql).append(" ) temp ").toString();
        Query getCount = getSession().createSQLQuery(hql);
        int count = ((Number) getCount.uniqueResult()).intValue();
        return count;
    }

    public void saveDuChatask(String sysid) {
        ResultSet set = null;
        try {
            Connection conn = SessionFactoryUtils.getDataSource(sessionFactory).getConnection();
            String call = "{call createTask(?) }";
            CallableStatement cs = conn.prepareCall(call);
            cs.setString(1, sysid);
            cs.execute();
            conn.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    //获取所有未发送短信的
    public List<SupervisionTask> getNotSendStatue() {

        GenericQuery query = createQuery("t");
        query.append(" and t.aaa002= '0' and t.aaa001='1' ");
        return query.listResult();

    }

    //我的消息
    public List<SupervisionTask> getmyTasks(BnpUser user) {
        GenericQuery query = createQuery("obj");
        query.append("and obj.aaa001 ='1' "); //有效标志 1 有效
        query.append(" and obj.resPhone =:resPhone ").setParam("resPhone", user.getPhone())
                .append(" or obj.operatorPhone =:operatorPhone ").setParam("operatorPhone", user.getPhone())
                .append(" or obj.groupId like:groupId ").setParam("groupId", StringUtils.getLikewords(user.getGroupCode()));

        return query.listResult();
    }

    /**
     * 查询（条件后面自行补充）
     *
     * @param filter
     * @return
     */
    public List<SupervisionTask> querySupervisionTaskByFilter(SupervisionTaskFilter filter) {
        GenericQuery query = createQuery("obj");
        if (StringUtils.isNotBlank(filter.getProjectId())) {
            query.append(" and projectId =:projectId").setParam("projectId", filter.getProjectId());
        }
        if (StringUtils.isNotBlank(filter.getNodeId())) {
            query.append(" and nodeId =:nodeId").setParam("nodeId", filter.getNodeId());
        }
        if (StringUtils.isNotBlank(filter.getNodeSysId())) {
            query.append(" and nodeSysId =:nodeSysId").setParam("nodeSysId", filter.getNodeSysId());
        }
        if (StringUtil.isNotBlank(filter.getEffectFlag())) {
            if ("1".equals(filter.getEffectFlag())) {
                SimpleDateFormat f = new SimpleDateFormat("yyyy-MM-dd");
                Calendar c = Calendar.getInstance();
                c.add(Calendar.DAY_OF_MONTH, -7);
                String dateTime = f.format(c.getTime());
                query.append(" and obj.createDate >=:createDate").setParam("createDate", dateTime);
            }
        }
        query.append(" and obj.aaa001 ='1' "); //有效标志 1 有效
        query.append(" ORDER BY obj.createDate desc");
        return query.listResult();
    }


    public List<SupervisionTask> getDoing(String name, String role) {
        GenericQuery query = createQuery("obj");
        query.append(" and taskResult =:taskResult").setParam("taskResult", "0");       //未完成
        query.append(" and aaa001 =:aaa001").setParam("aaa001", "1");                   //有效任务
        if ("经办人".equals(role)) {
            query.append(" and( operatorUser =:operatorUser or inspectors like:inspectors )")
                    .setParam("operatorUser", name)
                    .setParam("inspectors", StringUtils.getLikewords(name));
        } else {
            query.append(" and resUser =:resUser").setParam("resUser", name);
        }
        return query.listResult();
    }
}
